const exec = require('../db/mysql')

const add = (dealer) => {
  const sql = `insert into dealer(dealer_name,saler_name) values (?,?)`
  const params = [dealer.dealer_name,dealer.saler_name]
  return exec(sql,params).then(insert=>{
    return insert
  })
}

const addPerdayData = (dealer) => {
  let B2Bcurrent_sale_in = dealer.B2Bcurrent_sale_in || 0
  let B2Bsummation_sale_in = dealer.B2Bsummation_sale_in || 0
  let BPcurrent_sale_in = dealer.BPcurrent_sale_in || 0
  let BPsummation_sale_in = dealer.BPsummation_sale_in || 0
  const sql = `insert into dealer(dealer_name,saler_name,B2Bcurrent_sale_in,B2Bsummation_sale_in,BPcurrent_sale_in,BPsummation_sale_in) values(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE B2Bcurrent_sale_in=values(B2Bcurrent_sale_in), B2Bsummation_sale_in=values(B2Bsummation_sale_in) , BPcurrent_sale_in=values(BPcurrent_sale_in), BPsummation_sale_in=values(BPsummation_sale_in)`
  const params = [dealer.dealer_name,dealer.saler_name,B2Bcurrent_sale_in,B2Bsummation_sale_in,BPcurrent_sale_in,BPsummation_sale_in]
  return exec(sql,params).then(insert=>{
    return insert
  })
}

const addPerMonthList = (rows) =>{
  let paramsList =  []
  rows.forEach(item => {
    let rowList = [];
    let s_region = item.s_region;
    let dealer_name = item.dealer_name
    let saler_name = item.saler_name
    let b2b_original_stock = item.b2b_original_stock
    let bp_original_stock = item.bp_original_stock
    let bp_target = item.bp_target
    rowList.push(dealer_name)
    rowList.push(s_region)
    rowList.push(saler_name)
    rowList.push(b2b_original_stock)
    rowList.push(bp_original_stock)
    rowList.push(bp_target)
    paramsList.push(rowList)
  })

  const sql = `insert into dealer(dealer_name,s_region,saler_name,b2b_original_stock,bp_original_stock,bp_target) values ? ON DUPLICATE KEY UPDATE s_region=values(s_region), saler_name=values(saler_name) , b2b_original_stock=values(b2b_original_stock), bp_original_stock=values(bp_original_stock), bp_target=values(bp_target)`
  return exec(sql,[paramsList]).then(insert=>{
    return insert
  })
}

const addPerdayList = (rows) => {
  let paramsList =  []
  rows.forEach(item => {
    let rowList = [];
    let dealer_name = item.dealer_name
    // let saler_name = item.saler_name || "周亚峰"
    let B2Bcurrent_sale_in = item.b2bcurrent_sale_in || 0
    let B2Bsummation_sale_in = item.b2bsummation_sale_in || 0
    let BPcurrent_sale_in = item.bpcurrent_sale_in || 0
    let BPsummation_sale_in = item.bpsummation_sale_in || 0
    let b2b_Original_stock = item.b2b_Original_stock || 0
    let b2bcurrent_sale_in = item.b2bcurrent_sale_in || 0
    rowList.push(dealer_name)
    // rowList.push(saler_name)
    rowList.push(B2Bcurrent_sale_in)
    rowList.push(B2Bsummation_sale_in)
    rowList.push(BPcurrent_sale_in)
    rowList.push(BPsummation_sale_in)
    paramsList.push(rowList)
  })

  // const sql = `insert into dealer(dealer_name,saler_name,B2Bcurrent_sale_in,B2Bsummation_sale_in,BPcurrent_sale_in,BPsummation_sale_in) values(?,?,?,?,?,?) ON DUPLICATE KEY UPDATE B2Bcurrent_sale_in=values(B2Bcurrent_sale_in), B2Bsummation_sale_in=values(B2Bsummation_sale_in) , BPcurrent_sale_in=values(BPcurrent_sale_in), BPsummation_sale_in=values(BPsummation_sale_in)`
  // const params = [dealer.dealer_name,dealer.saler_name,B2Bcurrent_sale_in,B2Bsummation_sale_in,BPcurrent_sale_in,BPsummation_sale_in]
  const sql = `insert into dealer(dealer_name,b2bcurrent_sale_in,b2bsummation_sale_in,bpcurrent_sale_in,bpsummation_sale_in) values ? ON DUPLICATE KEY UPDATE b2bcurrent_sale_in=values(b2bcurrent_sale_in), b2bsummation_sale_in=values(b2bsummation_sale_in) , bpcurrent_sale_in=values(bpcurrent_sale_in), bpsummation_sale_in=values(bpsummation_sale_in)`
  // const sql = `insert into dealer(dealer_name,saler_name,b2bcurrent_sale_in,b2bsummation_sale_in,bpcurrent_sale_in,bpsummation_sale_in) values ? ON DUPLICATE KEY UPDATE B2Bcurrent_sale_in=?, b2bsummation_sale_in=?, bpcurrent_sale_in=?, bpsummation_sale_in=?`
  // const sql = `insert into dealer(dealer_name,saler_name,b2bcurrent_sale_in,b2bsummation_sale_in,bpcurrent_sale_in,bpsummation_sale_in) values ?`
  return exec(sql,[paramsList]).then(insert=>{
    return insert
  })
}

const dealerSaleList = (timestamp)=>{
  const preTime = getSpecialTime(24*60*60*1000);
  const currentTime = getSpecialTime(0);
  const preStartTime = `${preTime} 12:00:00`
  const currentEndTime = `${currentTime} 10:00：00`

  // const sql = `select * b2b_Original_stock + b2bsummation_sale_in - sorder.summation_sale_out as b2bleft_stock from dealer inner join sorder on dealer.dealer_name = sorder.dealer_name`
  const sql =`select s_region as "区域",dealer.dealer_name as "经销商",saler_name as "业务",b2b_Original_stock as "B2B期初库存",b2bcurrent_sale_in as "B2B当天sellin",b2bsummation_sale_in as "B2B累计sellin",current_sale_out as "B2B当天sellout",summation_sale_out as "B2B累计sellout",b2b_Original_stock+b2bsummation_sale_in-sorder.summation_sale_out as "B2B剩余库存",bp_Original_stock as "BP期初库存",bpcurrent_sale_in as "BP当天sellin",bpsummation_sale_in as "BP累计sellin",bpcurrent_sale_out as "BP当天sellout",bpsummation_sale_out as "BP累计sellout",bp_Original_stock+bpsummation_sale_in-sorder.bpsummation_sale_out as "BP剩余库存",bp_target as "BP考核目标",CONCAT(ROUND(bpsummation_sale_in/bp_Target*100),"%") as "BP达成率",if(update_time > 0,update_time>='${preStartTime}' && update_time<='${currentEndTime}',create_time>='${preStartTime}' && create_time<='${currentEndTime}') as "has_update" from dealer left join sorder on dealer.dealer_name = sorder.dealer_name`
  return exec(sql,null).then(result=>{
        return result
    })
}

const getSpecialTime = (preTimestamp) => {
  var curDate = new Date();
  var preDate = new Date(curDate.getTime() - preTimestamp)
  var preYear = preDate.getFullYear().toString();
  var preMonth = (preDate.getMonth()+1).toString().padStart(2,'0');
  var preDay = preDate.getDate().toString().padStart(2,'0');
  var time = `${preYear}-${preMonth}-${preDay}`;
  return time;
}



module.exports = {
    add,addPerdayData,addPerdayList,dealerSaleList,addPerMonthList
}